package org.elastic.compreplatform.codegener.controller;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.StringUtils;
import org.elastic.compreplatform.codegener.model.SysConfig;
import org.elastic.compreplatform.codegener.service.ICodegenerService;
import org.elastic.compreplatform.common.constant.DbEnum;
import org.elastic.compreplatform.common.model.PageHelper;
import org.elastic.compreplatform.common.model.RespMsg;
import org.elastic.compreplatform.common.util.PageHelperUtil;
import org.elastic.compreplatform.common.util.SeqUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.github.pagehelper.Page;
import com.xiaoleilu.hutool.db.DbUtil;
import com.xiaoleilu.hutool.db.SqlRunner;
import com.xiaoleilu.hutool.db.ds.simple.SimpleDataSource;

import cn.org.codegenframework.generator.GeneratorFacade;
import cn.org.codegenframework.generator.GeneratorProperties;

/**
 * ClassName: CodegenerController 
 * @Description: 代码生成控制类
 * @author JornTang
 * @date 2018年1月28日
 */
@Controller
@RequestMapping("/codegener")
public class CodegenerController {
	private static Logger log = LoggerFactory.getLogger(CodegenerController.class);
	@Resource
	private ICodegenerService codegenerService;
	public static void main(String[] args) {
		try {
			GeneratorFacade g = new GeneratorFacade();
			//设置数据库相关属性
			GeneratorProperties.setProperty("namespace", "com.test");
			GeneratorProperties.setProperty("basepackage", "com.test");
			GeneratorProperties.setProperty("tableRemovePrefixes", "");
			GeneratorProperties.setProperty("jdbc.username", "root");
			GeneratorProperties.setProperty("jdbc.password", "root");
			GeneratorProperties.setProperty("jdbc.url", "jdbc:mysql://127.0.0.1:3306/elastic_compre?useUnicode=true&characterEncoding=UTF-8");
			GeneratorProperties.setProperty("jdbc.driver", "com.mysql.jdbc.Driver");
			GeneratorProperties.setProperty("jdbc.schema", "elastic_compre");
			GeneratorProperties.setProperty("outRoot", "H:\\IDE-workspace\\shop-space\\compreplatform-parent\\compreplatform-admin\\build\\generator-output");
			//获取项目动态绝对路径 
			//String path = request.getServletContext().getRealPath("");
			//GeneratorProperties.setProperty("outRoot", path + "\\build\\generator-output");
			//删除已输出的代码
			g.deleteOutRootDir();
			//根据模板生成代码
			g.generateByTable("db_config", "H:\\IDE-workspace\\shop-space\\compreplatform-parent\\compreplatform-admin\\template");
			//直接打开生成代码的文件
			Runtime.getRuntime().exec("cmd.exe /c start " + GeneratorProperties.getRequiredProperty("outRoot"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * @Description: 跳转到生成代码配置页面
	 * @return   
	 * @return ModelAndView  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月6日
	 */
	@RequestMapping("/toTableConf.do")
	public ModelAndView toTableConf(SysConfig SysConfig, String tableName){
		ModelAndView view = new ModelAndView();
    	view.setViewName("codegener/table_conf");
    	view.addObject("sysConfig", SysConfig);
    	view.addObject("tableName", tableName);
        return view;
	}
	/**
	 * @Description: 查询数据库schema所有表
	 * @return   
	 * @return RespMsg  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月6日
	 */
	@RequestMapping("/searchTablelist.do")
	@ResponseBody
	public PageHelper searchTablelist(SysConfig SysConfig, PageHelper helper){
		//获取数据库配置
		SysConfig config = codegenerService.searchDbconfById(SysConfig);
		if(config == null){
			return helper;
		}
		config.setTableName(SysConfig.getTableName());
		//获取数据源连接
		SimpleDataSource dataSource = new SimpleDataSource(config.getDbUrl(), config.getUsername(), config.getPassword(), config.getDbDriver());
		SqlRunner sqlRunner = DbUtil.newSqlRunner(dataSource);
		try {
			String sql = doHandleSql(config);
			List count = sqlRunner.query(sql, null);
			helper.setCount(count.size());
			//分页查询sql
			String pageSql = doHandlePageSql(config, sql, helper);
			List list = sqlRunner.query(pageSql, null);
			
			helper.setData(list);
		} catch (SQLException e) {
			log.error("查询数据库schema所有表异常", e);
		}
		return helper;
	}
	
	/**
	 * @Description: 跳转到代码生成页面
	 * @return   
	 * @return ModelAndView  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月6日
	 */
	@RequestMapping("/toCodeBuild.do")
	public ModelAndView toCodeBuild(){
		ModelAndView view = new ModelAndView();
		//获取所有的db配置
		List<SysConfig> configs = codegenerService.searchAllDbconf();
    	view.setViewName("codegener/code_build");
    	view.addObject("sysConfigs", configs);
        return view;
	}
	/**
	 * @Description: 删除数据库配置
	 * @param request
	 * @param SysConfig
	 * @return   
	 * @return RespMsg  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月3日
	 */
	@RequestMapping("/deleteDbconf.do")
	@ResponseBody
	public RespMsg deleteDbconf(HttpServletRequest request, SysConfig SysConfig){
		return codegenerService.deleteDbconf(SysConfig);
	}
	/**
	 * @Description: 查询dbconf集合
	 * @param request
	 * @param model
	 * @return
	 * @throws IOException   
	 * @return ModelAndView  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月1日
	 */
    @RequestMapping("/dblist.do")
    @ResponseBody
    public PageHelper searchDbconflist(HttpServletRequest request, SysConfig SysConfig, PageHelper page)throws IOException {
    	SysConfig.setDbType("'Oracle','Mysql'");
    	Page<SysConfig> list = (Page<SysConfig>) this.codegenerService.searchDbconflist(SysConfig, page);
    	return PageHelperUtil.doHandlePage(list);
    }
	/**
	 * @Description: 跳转到db添加或修改页面
	 * @param request
	 * @param model
	 * @return
	 * @throws IOException   
	 * @return ModelAndView  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月1日
	 */
    @RequestMapping("/toDbconfAddOrUpdate.do")
    public ModelAndView toDbconfAddOrUpdate(HttpServletRequest request, SysConfig SysConfig)throws IOException {
    	ModelAndView view = new ModelAndView();
    	if(SysConfig!= null && SysConfig.getId()!= null){
    		SysConfig =codegenerService.searchDbconfById(SysConfig);
    	}
    	view.setViewName("codegener/dbconf_addOrUpdate");
    	view.addObject("sysConfig", SysConfig);
        return view;
    }
	/**
	 * @Description: 跳转到db配置界面
	 * @param request
	 * @param model
	 * @return
	 * @throws IOException   
	 * @return ModelAndView  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月1日
	 */
    @RequestMapping("/toCodegenerDbconf.do")
    public ModelAndView toCodegenerDbconf(HttpServletRequest request, Model model)throws IOException {
    	ModelAndView view = new ModelAndView();
    	view.setViewName("codegener/dbconf");
        return view;
    }
    
    /**
     * @Description: 添加或修改db配置
     * @param request
     * @param model
     * @return
     * @throws IOException   
     * @return ModelAndView  
     * @throws
     * @author JornTang
     * @date 2018年2月2日
     */
    @RequestMapping("/saveOrUpdateDbconf.do")
    @ResponseBody
    public RespMsg saveOrUpdateDbconf(HttpServletRequest request, Model model, SysConfig SysConfig)throws IOException {
    	if(SysConfig.getId() == null){
    		//获取主键
        	SysConfig.setId(SeqUtil.getSeq());
    	}
    	//根据数据库类型设置数据库驱动
    	doHandleDriver(SysConfig);
        return codegenerService.saveOrUpdateDbconf(request, SysConfig);
    }
    /**
     * @Description: 根据数据库类型设置数据库驱动
     * @param SysConfig   
     * @return void  
     * @throws
     * @author JornTang
     * @date 2018年2月3日
     */
	private void doHandleDriver(SysConfig SysConfig) {
		if(SysConfig!= null && StringUtils.isNotEmpty(SysConfig.getDbType())){
			String dbtype = SysConfig.getDbType();
			for (DbEnum e : DbEnum.values()) {
				String type = e.getType();
				if(type.equals(dbtype)){
					SysConfig.setDbDriver(e.getDriver());
					return;
				}
			}   
		}
	}
	/**
     * @Description: 查询总数sql
     * @param SysConfig   
     * @return void  
     * @throws
     * @author JornTang
     * @date 2018年2月3日
     */
	private String doHandleSql(SysConfig SysConfig) {
		if(SysConfig!= null && StringUtils.isNotEmpty(SysConfig.getDbType())){
			String dbtype = SysConfig.getDbType();
			for (DbEnum e : DbEnum.values()) {
				String type = e.getType();
				if(type.equals(dbtype)){
					String sql = e.getSql().replace("{}", SysConfig.getDbSchema().toUpperCase());
					if(StringUtils.isNotEmpty(SysConfig.getTableName())){
						sql += " and t.TABLE_NAME like '%" + SysConfig.getTableName() + "%'";
					}
					return sql;
				}
			}   
		}
		return null;
	}
	/**
	 * @Description: 分页查询sql
	 * @param config
	 * @param helper
	 * @return   
	 * @return String  
	 * @throws
	 * @author JornTang
	 * @date 2018年2月7日
	 */
	private String doHandlePageSql(SysConfig conf, String sql, PageHelper helper) {
		//mysql
		if(DbEnum.MYSQL.getType().equals(conf.getDbType())){
			return sql + " limit " + helper.getOffset() + "," + helper.getLimit();
		//oracle
		}else if(DbEnum.ORACLE.getType().equals(conf.getDbType())){
			return "select * from (select a1.*, rownum rn from (" + sql + ") a1 " + " where  rownum <=  "+(helper.getOffset() + helper.getLimit()) + ") where rn >=" + helper.getOffset();
		}
		return null;
	}
}
